from typing import Union, Tuple, Type, Optional, List
from sqlalchemy.orm import Session
from db import models
from schemas.flower import CreateFlowerTypeRequest, UpdateFlowerTypeRequest, CreateFlowerRequest, \
    UpdateFlowerRequest, DeleteFlowerRequest
from sqlalchemy import and_, desc


class Flower:
    @staticmethod
    def create(db: Session, data: CreateFlowerRequest) -> models.FlowerType:
        item = models.Flower(
            name=data.name,
            description=data.description,
            created_on=data.created_on,
            created_by=data.created_by,
            images=data.images,
            original_price=data.original_price,
            price=data.price,
            receiver=data.receiver,
            material=data.material,
            size=data.size,
            occasion=data.occasion,
            type=data.type,
            tags=data.tags,
            presale=data.presale,
            presale_start_date=data.presale_start_date,
            presale_end_date=data.presale_end_date,
            presale_delivery_start_date=data.presale_delivery_start_date,
            presale_delivery_end_date=data.presale_delivery_end_date,
        )
        db.add(item)
        db.commit()
        return item

    @staticmethod
    def gets(
            db: Session,
            page: int,
            page_size: int,
            receiver: Optional[int] = None,
            material: Optional[int] = None,
            size: Optional[int] = None,
            flower_type: Optional[int] = None,
            kw: Optional[str] = None,
            presale: Optional[bool] = None) -> Tuple[int, list[Type[models.Flower]]]:

        filter_params = {}

        if receiver is not None:
            filter_params["receiver"] = receiver
        if material is not None:
            filter_params["receiver"] = material
        if size is not None:
            filter_params["size"] = size
        if flower_type is not None:
            filter_params["type"] = flower_type
        if presale is not None:
            filter_params["presale"] = presale

        if kw is None:
            filter_ = and_(models.Flower.status != 0)

            count = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).count()

            data = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).offset(page * page_size).limit(page_size).all()
        else:
            filter_ = and_(models.Flower.name.like(f"%{kw}%"), models.Flower.status != 0)

            count = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).count()

            data = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).offset(page * page_size).limit(page_size).all()
        return count, data

    @staticmethod
    def get_list(
            db: Session,
            page: int,
            page_size: int,
            sort: int,
            kw: Optional[str] = None,
            receiver: Optional[int] = None,
            material: Optional[int] = None,
            size: Optional[int] = None,
            flower_type: Optional[int] = None,
            occasion: Optional[int] = None) -> Tuple[int, list[Type[models.Flower]]]:

        filter_params = {}

        if receiver is not None:
            filter_params["receiver"] = receiver
        if material is not None:
            filter_params["material"] = material
        if size is not None:
            filter_params["size"] = size
        if flower_type is not None:
            filter_params["type"] = flower_type
        if occasion is not None:
            filter_params["occasion"] = occasion

        order_by = models.Flower.id

        if sort == 1:
            order_by = models.Flower.id
        elif sort == 2:
            order_by = models.Flower.price
        elif sort == 3:
            order_by = desc(models.Flower.price)
        elif sort == 4:
            order_by = desc(models.Flower.sales)

        if kw is None:
            filter_ = and_(models.Flower.status == 1)

            count = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).count()

            data = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).order_by(order_by).offset(page * page_size).limit(page_size).all()
        else:
            filter_ = and_(models.Flower.name.like(f"%{kw}%"), models.Flower.status == 1)

            count = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).count()

            data = db.query(models.Flower).filter_by(**filter_params).filter(
                filter_
            ).order_by(order_by).offset(page * page_size).limit(page_size).all()
        return count, data

    @staticmethod
    def get_items(db: Session):
        return db.query(
            models.Flower.id,
            models.Flower.name,
            models.Flower.images,
            models.Flower.original_price,
            models.Flower.price,
            models.Flower.receiver,
            models.Flower.material,
            models.Flower.size,
            models.Flower.type,
            models.Flower.occasion,
            models.Flower.tags
        ).filter_by(status=1).all()

    @staticmethod
    def get(db: Session, id_: int) -> Union[models.Flower, None]:
        return db.query(models.Flower).get(id_)

    @staticmethod
    def update(db: Session, data: UpdateFlowerRequest) -> None:
        db.query(models.Flower).filter_by(id=data.id).update({
            "name": data.name,
            "description": data.description,
            "updated_on": data.updated_on,
            "updated_by": data.updated_by,
            "images": data.images,
            "original_price": data.original_price,
            "price": data.price,
            "receiver": data.receiver,
            "material": data.material,
            "size": data.size,
            "type": data.type,
            "tags": data.tags,
            "presale": data.presale,
            "presale_start_date": data.presale_start_date,
            "presale_end_date": data.presale_end_date,
            "presale_delivery_start_date": data.presale_delivery_start_date,
            "presale_delivery_end_date": data.presale_delivery_end_date
        })
        db.commit()

    @staticmethod
    def delete(db: Session, data: DeleteFlowerRequest) -> None:
        db.query(models.Flower).filter_by(id=data.id).update({
            "status": 0,
            "updated_on": data.updated_on,
            "updated_by": data.updated_by,
        })
        db.commit()

    @staticmethod
    def count_by_type(db: Session, type_id: int):
        return db.query(models.Flower).filter(and_(
            models.Flower.status != 0,
            models.Flower.type == type_id
        )).count()


class FlowerType:
    @staticmethod
    def create(db: Session, data: CreateFlowerTypeRequest) -> models.FlowerType:
        item = models.FlowerType(
            name=data.name,
            created_on=data.created_on,
            created_by=data.created_by,
        )
        db.add(item)
        db.commit()
        return item

    @staticmethod
    def update(db: Session, data: UpdateFlowerTypeRequest) -> None:
        db.query(models.FlowerType).filter_by(id=data.id).update({
            "name": data.name,
            "updated_on": data.updated_on,
            "updated_by": data.updated_by
        })
        db.commit()

    @staticmethod
    def get_by_name(db: Session, name: str) -> Union[models.FlowerType, None]:
        return db.query(models.FlowerType).filter_by(name=name, status=1).first()

    @staticmethod
    def gets(db: Session,
             page: int,
             page_size: int,
             kw: Optional[str] = None) -> Tuple[int, list[Type[models.FlowerType]]]:
        if kw is None:
            count = db.query(models.FlowerType).filter_by(status=1).count()

            data = db.query(models.FlowerType).filter_by(status=1).offset(page * page_size).limit(page_size).all()
        else:
            count = db.query(models.FlowerType).filter_by(status=1).filter(
                models.FlowerType.name.like(f"%{kw}%")
            ).count()

            data = db.query(models.FlowerType).filter_by(status=1).filter(
                models.FlowerType.name.like(f"%{kw}%")
            ).offset(page * page_size).limit(page_size).all()
        return count, data

    @staticmethod
    def get_all(db: Session) -> List[Type[models.FlowerType]]:
        return db.query(models.FlowerType).filter_by(status=1).all()

    @staticmethod
    def get_items(db: Session) -> List[Type[models.FlowerType]]:
        return db.query(
            models.FlowerType.id,
            models.FlowerType.name
        ).filter_by(status=1).all()

    @staticmethod
    def delete(db: Session, item_id: int) -> None:
        db.query(models.FlowerType).filter_by(id=item_id).update({
            "status": 0,
        })
        db.commit()
